package com.cgnpc.scp.common.utils;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.extractor.XSSFEventBasedExcelExtractor;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 * Excel的工具类
 */
public class ExcelUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * keep 100 rows in memory, exceeding rows will be flushed to disk
     */
    public static final int WB_WINDOWSIZE = 500;

    public static final int SHEET_MAXROWS = 100 * 10000;

    public static void closeWorkbook(Workbook wb) {
        if (wb != null) {
            if (wb instanceof SXSSFWorkbook) {
                SXSSFWorkbook sxssfWorkbook = (SXSSFWorkbook) wb;

                try {
                    // dispose of temporary files backing this workbook on disk
                    // 删除磁盘上的生成的临时文件
                    sxssfWorkbook.dispose();
                } catch (Exception e) {
                    LOGGER.error("closeWorkbook.SXSSFWorkbook.dispose.error.", e);
                }
            }

            try {
                wb.close();
            } catch (Exception e) {
                LOGGER.error("closeWorkbook.Workbook.close.error.", e);
            }

        }
    }

    public static void closeOPCPackage(OPCPackage opcPackage) {
        if (opcPackage != null) {
            try {
                opcPackage.close();
            } catch (Exception e) {
                LOGGER.error("closeOPCPackage.error;", e);
            }
        }
    }

    public static void closeExcelExtractor(XSSFEventBasedExcelExtractor excelExtractor) {
        if (excelExtractor != null) {
            try {
                excelExtractor.close();
            } catch (Exception e) {
                LOGGER.error("closeExcelExtractor.error;", e);
            }
        }
    }

    /**
     * 设置cell的值，建议只传递String
     *
     * @param cell      cell
     * @param cellValue cellValue
     * @param sdf       日期格式化类
     */
    public static void setCellValue(Cell cell, Object cellValue, SimpleDateFormat sdf) {
        if (cellValue == null) {
            cell.setCellValue((String) null);
        } else if (cellValue instanceof String) {
            cell.setCellValue((String) cellValue);
        } else if (cellValue instanceof Double) {
            cell.setCellValue(cellValue.toString());
        } else if (cellValue instanceof Date && sdf != null) {
            cell.setCellValue(sdf.format((Date) cellValue));
        } else if (cellValue instanceof Calendar && sdf != null) {
            cell.setCellValue(sdf.format(((Calendar) cellValue).getTime()));
        } else {
            // 默认调用toString方法
            cell.setCellValue(cellValue.toString());
        }
    }

    /**
     * 设置cell的值，建议只传递String
     *
     * @param cell      cell
     * @param cellValue cellValue
     */
    public static void setCellValue(Cell cell, Object cellValue) {
        setCellValue(cell, cellValue, null);
    }

    /**
     * 获取单元格内容
     *
     * @param cell 单元格
     * @return 返回String字符串，包括数字等等
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        CellType cellType = cell.getCellType();

        if (cellType == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cellType == CellType.NUMERIC) {
            int numericCellValue = (int)cell.getNumericCellValue();
            return "" + numericCellValue;
        } else if (cellType == CellType.BOOLEAN) {
            boolean cellValue = cell.getBooleanCellValue();
            return "" + cellValue;
        }
        return null;
    }

}
